/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.insurance.cis.controllers;

import com.insurance.cis.connection.CisConnection;
//import com.insurance.cis.models.Product;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;


public class ProductController {
    public static Vector readProduct() {
        Vector vt = new Vector();
        try {
            String sql = "Select * from Product";
            System.out.println(sql);
            Statement st = CisConnection.getInstance().getSqlConnection().createStatement();
            ResultSet rs = st.executeQuery(sql);

            while (rs.next()) {
                String sql1 = "Select ProductCatDesc from ProductCategory where ProductCatID = ?";
                PreparedStatement pst = CisConnection.getInstance().getSqlConnection().prepareStatement(sql1);
                pst.setInt(1, rs.getInt("ProductCatID"));
                ResultSet rs1 = pst.executeQuery();
                rs1.next();
                Vector temp = new Vector();
                temp.add(rs.getString("ProductID"));
                temp.add(rs.getString("ProductName"));
                temp.add(rs.getString("ProductDesc"));
                temp.add(rs1.getString("ProductCatDesc"));
                vt.add(temp);              
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return vt;
        }
    }
    
    public static Vector SearchProduct(String strSearch, int type) {
        Vector vt = new Vector();
        String sql;
        boolean found = false;
        if (type == 1) {
            sql = "select * from Product where ProductID like '%" + strSearch + "%'";
        } else {
            sql = "select * from Product where ProductName like '%" + strSearch + "%'";
        }
        try {
            ResultSet rs = CisConnection.getInstance().getSqlConnection().createStatement().executeQuery(sql);
            while (rs.next()) {
                found = true;
                String sql1 = "Select ProductCatDesc from ProductCategory where ProductCatID = ?";
                PreparedStatement pst = CisConnection.getInstance().getSqlConnection().prepareStatement(sql1);
                pst.setInt(1, rs.getInt("ProductCatID"));
                ResultSet rs1 = pst.executeQuery();
                rs1.next();
                
                Vector temp = new Vector();
                temp.add(rs.getString("ProductID"));
                temp.add(rs.getString("ProductName"));
                temp.add(rs.getString("ProductDesc"));
                temp.add(rs1.getString("ProductCatDesc"));
                vt.add(temp);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return vt;
        }
    }
//    public static String insertProduct(Product pro) {
//
//        Vector vt = readProduct();
//        String message = "";
//        for (int i = 0; i < vt.size(); i++) {
//            Vector temp = (Vector) vt.get(i);
//            if (temp.get(0).toString().equals(pro.getProductID())||temp.get(1).toString().equals(pro.getProductName())) {
//                return message = "Cannot insert duplicate product";
//            }
//        }
//        String sql = "insert into Product(ProductID,ProductCatID,ProductName,ProductDesc) values(?,?,?,?)";
//
//        try {
//            PreparedStatement pst = CisConnection.getInstance().getSqlConnection().prepareStatement(sql);
//            pst.setString(1, pro.getProductID());
//            pst.setInt(2, pro.getProductCatID());
//            pst.setString(3, pro.getProductName());
//            pst.setString(4, pro.getDescription());
//
//            pst.executeUpdate();
//
//            message = "Insert Sucessfuly ";
//
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//            message = "Insert Failure";
//
//        } finally {
//            return message;
//        }
//    }
}